﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports BusinessRule.DTO
Imports System.Configuration
Public Class uMES_OperationPlanBusiness
    Implements uMES_OperationPlanIBusiness

    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

    ''' <summary>
    ''' 变更月作业计划状态
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveMonthOperationPlanListInfo(ByVal QueryField As Dictionary(Of String, String), ByRef resultInfo As String) As Boolean Implements uMES_OperationPlanIBusiness.SaveMonthOperationPlanListInfo
        Dim txnmaint = "MfgPlanForMonthMaint"
        Dim clientData As New CamstarClientAPI(QueryField("UserName"), QueryField("Password"))
        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity = New ClientAPIEntity()

        dataEntity = SetClientAPIEntity(QueryField, "ProductNO")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "ProductName")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "PlanMonth")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "WorkCenter")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "Family")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "OneWeekName")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "OneWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "OneWeekNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(QueryField, "TwoWeekName")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "TwoWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "TwoWeekNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(QueryField, "ThreeWeekName")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "ThreeWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "ThreeWeekNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(QueryField, "FourWeekName")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "FourWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "FourWeekNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(QueryField, "FiveWeekName")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "FiveWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "FiveWeekNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(QueryField, "NextMonthName")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "NextMonthQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "NextMonthNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(QueryField, "NextMonth2Name")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "NextMonth2Qty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(QueryField, "NextMonth2Note")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(QueryField, "Notes")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = QueryField("UniqueName")
        dataEntityList.Add(dataEntity)

        'SaveMonthOperationPlanListInfo = clientData.CreateModelByStandard(txnmaint + "Doc", txnmaint, "New", dataEntityList, resultInfo)

        If QueryField("MfgPlanForMonthName") Is Nothing Then
            SaveMonthOperationPlanListInfo = clientData.CreateModelByStandard(txnmaint + "Doc", txnmaint, "New", dataEntityList, resultInfo)
        Else
            SaveMonthOperationPlanListInfo = clientData.UpdateModelByStandard(txnmaint + "Doc", txnmaint, QueryField("MfgPlanForMonthName"), "", dataEntityList, resultInfo)
        End If

        Return True
    End Function

    Private Function SetClientAPIEntity(ByVal QueryField As Dictionary(Of String, String), ByVal strName As String) As ClientAPIEntity
        Dim dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = strName
        dataEntity.ClientDataValue = QueryField(strName)

        Return dataEntity
    End Function

    Public Function GetMonthOperationPlan(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_OperationPlanIBusiness.GetMonthOperationPlan
        GetMonthOperationPlan = New uMESProcessResult()

        Dim where As String
        Dim year = QueryCondition("Year")
        Dim month = QueryCondition("Month")
        If month = "全部" Then
            where = " where mfg.planmonth like'%" & year & "%'"
        Else
            If month.Length = 1 Then
                month = "0" + month
            End If
            where = " where mfg.planmonth = '" & year & "-" & month & "'"
        End If

        Dim sb = New StringBuilder()
        'sb.AppendLine("select * from MfgPlanForMonth mfg where mfg.planmonth='" + yearandmonth + "'")
        sb.AppendLine(" select  '' family1,'' family2,'' family3, '' family4,'' family5,'' family6, ")
        sb.AppendLine(" '' family7,'' family8,'' family9, '' family10,'' family11,'' family12,'' month,mfg.*  from MfgPlanForMonth mfg")
        sb.AppendLine(where)

        Dim yearplanDT = dal.GetMfgOrderList(sb.ToString())
        yearplanDT.TableName = "yearplandt"
        GetMonthOperationPlan.DBDataSet.Tables.Add(yearplanDT)

    End Function

    Public Function GetProduct(ByVal QueryCondition As String, ByVal planmonth As String) As uMESProcessResult Implements uMES_OperationPlanIBusiness.GetProduct
        GetProduct = New uMESProcessResult

        Dim sb = New StringBuilder()
        sb.AppendLine("select '' parentproductname,pb.productname,")
        sb.AppendLine(" mplan.mfgplanformonthname,mplan.planmonth,mplan.oneweekqty,mplan.oneweeknote,")
        sb.AppendLine(" mplan.twoweekqty,mplan.twoweeknote,mplan.threeweekqty,mplan.threeweeknote,")
        sb.AppendLine(" mplan.fourweekqty,mplan.fourweeknote,mplan.fiveweekqty,mplan.fiveweeknote,'0'gid,''pid,")
        sb.AppendLine(" mplan.oneweekspecid,mplan.twoweekspecid,mplan.threeweekspecid,mplan.fourweekspecid,mplan.fiveweekspecid,")
        sb.AppendLine(" sb1.specname onename, sb2.specname twoname, sb3.specname threename, sb4.specname fourname, sb5.specname fivename")
        sb.AppendLine(" from productbase pb")
        sb.AppendLine(" left join product p on p.productid = pb.revofrcdid")
        sb.AppendLine(" left join mfgplanformonth mplan on (mplan.productname=pb.productname and  mplan.planmonth='" + planmonth + "')")
        sb.AppendLine(" LEFT JOIN spec s1 on s1.specid =  mplan.oneweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb1 on sb1.specbaseid =s1.specbaseid")
        sb.AppendLine(" LEFT JOIN spec s2 on s2.specid =  mplan.twoweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb2 on sb2.specbaseid =s2.specbaseid")
        sb.AppendLine(" LEFT JOIN spec s3 on s3.specid =  mplan.threeweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb3 on sb3.specbaseid =s3.specbaseid")
        sb.AppendLine(" LEFT JOIN spec s4 on s4.specid =  mplan.fourweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb4 on sb4.specbaseid =s4.specbaseid")
        sb.AppendLine(" LEFT JOIN spec s5 on s5.specid =  mplan.fiveweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb5 on sb5.specbaseid =s5.specbaseid")

        sb.AppendLine(QueryCondition)

        Dim dt = dal.GetMfgOrderList(sb.ToString())
        GetProduct.DBTable = dt
    End Function

    Public Function GetBom(ByVal QueryCondition As String, ByVal planmonth As String) As uMESProcessResult Implements uMES_OperationPlanIBusiness.GetBom
        GetBom = New uMESProcessResult

        Dim sb = New StringBuilder()
        sb.AppendLine("select pb.productname parentproductname,nvl(pbsub.productname,pbsub1.productname)productname,pm.qtyrequired,")
        sb.AppendLine(" mplan.mfgplanformonthname,mplan.planmonth,mplan.oneweekqty,mplan.oneweeknote,")
        sb.AppendLine(" mplan.twoweekqty,mplan.twoweeknote,mplan.threeweekqty,mplan.threeweeknote,")
        sb.AppendLine(" mplan.fourweekqty,mplan.fourweeknote,mplan.fiveweekqty,mplan.fiveweeknote,''gid,''pid,")
        sb.AppendLine(" sb1.specname onename, sb2.specname twoname, sb3.specname threename, sb4.specname fourname, sb5.specname fivename")

        sb.AppendLine(" from productbase pb")
        sb.AppendLine(" left join product p on p.productid = pb.revofrcdid")
        sb.AppendLine(" left join bombase bb on bb.bombaseid=p.bombaseid")
        sb.AppendLine(" left join bom b on b.bomid = bb.revofrcdid")
        sb.AppendLine(" left join bom b1 on b1.bomid = p.bomid")
        sb.AppendLine(" left join bombase bb1 on bb1.bombaseid = b1.bombaseid")
        sb.AppendLine(" left join productmateriallistitem pm on pm.bomid=b.bomid or pm.bomid=b1.bomid")
        sb.AppendLine(" left join product psub on psub.productid=pm.productid")
        sb.AppendLine(" left join productbase pbsub on pbsub.productbaseid =psub.productbaseid")
        sb.AppendLine(" left join productbase pbsub1 on pbsub1.productbaseid= pm.productbaseid")
        sb.AppendLine(" left join product psub1 on psub1.productid= pbsub1.revofrcdid")
        sb.AppendLine(" left join mfgplanformonth mplan on (mplan.productname=pbsub.productname or mplan.productname=pbsub1.productname)")
        sb.AppendLine(" and  mplan.planmonth='" + planmonth + "'")
        sb.AppendLine(" LEFT JOIN spec s1 on s1.specid =  mplan.oneweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb1 on sb1.specbaseid =s1.specbaseid")
        sb.AppendLine(" LEFT JOIN spec s2 on s2.specid =  mplan.twoweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb2 on sb2.specbaseid =s2.specbaseid")
        sb.AppendLine(" LEFT JOIN spec s3 on s3.specid =  mplan.threeweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb3 on sb3.specbaseid =s3.specbaseid")
        sb.AppendLine(" LEFT JOIN spec s4 on s4.specid =  mplan.fourweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb4 on sb4.specbaseid =s4.specbaseid")
        sb.AppendLine(" LEFT JOIN spec s5 on s5.specid =  mplan.fiveweekspecid")
        sb.AppendLine(" LEFT JOIN specbase sb5 on sb5.specbaseid =s5.specbaseid")
        sb.AppendLine(QueryCondition)

        Dim dt = dal.GetMfgOrderList(sb.ToString())
        GetBom.DBTable = dt

    End Function

    Public Function SaveMonthPlan(ByVal paraDic As Dictionary(Of String, String), ByRef resultInfo As String) As Boolean Implements uMES_OperationPlanIBusiness.SaveMonthPlan
        Dim txnmaint = "MfgPlanForMonthMaint"
        Dim clientData As New CamstarClientAPI(paraDic("UserName"), paraDic("Password"))
        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity = New ClientAPIEntity()

        dataEntity = SetClientAPIEntity(paraDic, "ProductName", "ProductName")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(paraDic, "OneWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(paraDic, "OneWeekNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(paraDic, "TwoWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(paraDic, "TwoWeekNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(paraDic, "ThreeWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(paraDic, "ThreeWeekNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(paraDic, "FourWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(paraDic, "FourWeekNote")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(paraDic, "FiveWeekQty")
        dataEntityList.Add(dataEntity)
        dataEntity = SetClientAPIEntity(paraDic, "FiveWeekNote")
        dataEntityList.Add(dataEntity)

        '第一周工序
        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataVersion = paraDic("OneWeekSpecRevision")
        dataEntity.ClientDataName = "OneWeekSpecId"
        dataEntity.ClientDataValue = paraDic("OneWeekSpecId")
        dataEntityList.Add(dataEntity)
        '第二周工序
        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataVersion = paraDic("TwoWeekSpecRevision")
        dataEntity.ClientDataName = "TwoWeekSpecId"
        dataEntity.ClientDataValue = paraDic("TwoWeekSpecId")
        dataEntityList.Add(dataEntity)
        '第三周工序
        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataVersion = paraDic("ThreeWeekSpecRevision")
        dataEntity.ClientDataName = "ThreeWeekSpecId"
        dataEntity.ClientDataValue = paraDic("ThreeWeekSpecId")
        dataEntityList.Add(dataEntity)
        '第四周工序
        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataVersion = paraDic("FourWeekSpecRevision")
        dataEntity.ClientDataName = "FourWeekSpecId"
        dataEntity.ClientDataValue = paraDic("FourWeekSpecId")
        dataEntityList.Add(dataEntity)
        '第五周工序
        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataVersion = paraDic("FiveWeekSpecRevision")
        dataEntity.ClientDataName = "FiveWeekSpecId"
        dataEntity.ClientDataValue = paraDic("FiveWeekSpecId")
        dataEntityList.Add(dataEntity)

        dataEntity = SetClientAPIEntity(paraDic, "planmonth")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = paraDic("UniqueName")
        dataEntityList.Add(dataEntity)
        'Not String.IsNullOrWhiteSpace() Is Nothing
        If String.IsNullOrWhiteSpace(paraDic("MfgPlanForMonthName")) Then
            SaveMonthPlan = clientData.CreateModelByStandard(txnmaint + "Doc", txnmaint, "New", dataEntityList, resultInfo)
        Else
            SaveMonthPlan = clientData.UpdateModelByStandard(txnmaint + "Doc", txnmaint, paraDic("MfgPlanForMonthName"), "", dataEntityList, resultInfo)
        End If

    End Function

    Private Function SetClientAPIEntity(ByVal QueryField As Dictionary(Of String, String), ByVal keyOfDic As String, ByVal dataName As String) As ClientAPIEntity
        Dim dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = dataName
        dataEntity.ClientDataValue = QueryField(keyOfDic)

        Return dataEntity
    End Function

    Public Function GetWorkflowStep(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_OperationPlanIBusiness.GetWorkflowStep
        GetWorkflowStep = New uMESProcessResult

        Dim strSQL = "", strProductName As String
        strProductName = ""
        If QueryCondition.Keys.Contains("ProductName") = True Then
            strProductName = QueryCondition("ProductName").ToString()
        End If

        'strSQL = "SELECT a.*,specname ||'$'|| specrevision specro FROM (SELECT wfs.workflowstepid,wfs.workflowstepname,wfs.sequence,wfs.workflowid," & vbCrLf & _
        '         "nvl(sb1.specbaseid,sb2.specbaseid)specbaseid,nvl(s1.specid,s2.specid)specid,nvl(sb1.specname,sb2.specname)specname,nvl(s1.specrevision,s2.specrevision)specrevision" & vbCrLf & _
        '        "from productbase pb" & vbCrLf & _
        '        "INNER JOIN product p on p.productid = pb.revofrcdid" & vbCrLf & _
        '         "LEFT JOIN workflowbase wb1 on wb1.workflowbaseid = p.workflowbaseid" & vbCrLf & _
        '         "LEFT JOIN workflow w1 on w1.workflowid = wb1.revofrcdid" & vbCrLf & _
        '         "LEFT JOIN workflow w2 ON w2.workflowid = p.workflowid" & vbCrLf & _
        '         "LEFT JOIN workflowbase wb2 on wb2.workflowbaseid = w2.workflowbaseid" & vbCrLf & _
        '         "LEFT JOIN workflowstep  wfs on wfs.workflowid=w1.workflowid or wfs.workflowid=w2.workflowid" & vbCrLf & _
        '        "LEFT JOIN specbase sb1 on sb1.specbaseid = wfs.specbaseid" & vbCrLf & _
        '        "LEFT JOIN spec s1 on s1.specid = sb1.revofrcdid" & vbCrLf & _
        '        "LEFT JOIN spec s2 on s1.specid = wfs.specid" & vbCrLf & _
        '        "LEFT JOIN specbase sb2 on sb2.specbaseid = s1.specbaseid" & vbCrLf & _
        '         "WHERE pb.productname = '" & strProductName & "' ORDER BY wfs.sequence)A" & vbCrLf


        strSQL = "select  wt.specid,sb2.revofrcdid as specid2, sb1.specname,sb2.specname  as specname2,s.specrevision,s2.specrevision as specrevision2, wt.* from workflowstep wt " & vbCrLf & _
                 "left join workflow w on w.workflowid=wt.workflowid " & vbCrLf & _
                 "left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid " & vbCrLf & _
                 "left join product p on p.workflowbaseid=wb.workflowbaseid " & vbCrLf & _
                 "left join spec s on s.specid=wt.specid " & vbCrLf & _
                 "left join specbase sb1 on sb1.specbaseid=s.specbaseid " & vbCrLf & _
                 "left join specbase sb2 on sb2.specbaseid=wt.specbaseid " & vbCrLf & _
                 "left join spec s2 on s2.specid=sb2.revofrcdid " & vbCrLf & _
                 "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf & _
                 "where pb.productname= '" & strProductName & "' and wt.specbaseid is not null and wt.specid is not null order by wt.sequence " & vbCrLf
        Dim tmpSet As DataSet = dal.ExecuteQuery(strSQL)

        If tmpSet.Tables(0).Rows.Count = 0 Then

            strSQL = "select  wt.specid, sb2.revofrcdid as specid2, sb1.specname,sb2.specname as specname2,s.specrevision,s2.specrevision as specrevision2, wt.* from workflowstep wt " & vbCrLf & _
                     "left join product p on p.workflowid=wt.workflowid " & vbCrLf & _
                     "left join spec s on s.specid=wt.specid " & vbCrLf & _
                     "left join specbase sb1 on sb1.specbaseid=s.specbaseid " & vbCrLf & _
                     "left join specbase sb2 on sb2.specbaseid=wt.specbaseid " & vbCrLf & _
                     "left join spec s2 on s2.specid=sb2.revofrcdid " & vbCrLf & _
                     "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf & _
                     "where pb.productname= '" & strProductName & "' and wt.specbaseid is not null and wt.specid is not null   order by wt.sequence" & vbCrLf

            tmpSet = dal.ExecuteQuery(strSQL)
        End If
        Dim comm As uMESCommonBusiness = New uMESCommonBusiness()
        tmpSet.Tables(0).Columns.Add("DispSpecName")
        For Each row As DataRow In tmpSet.Tables(0).Rows
            If row("specID").ToString().Trim() = "0000000000000000" Then
                row("specID") = row("specid2")
            End If
            If row.IsNull("specname") = True Then
                row("specName") = row("specName2")
            End If
            row("DispSpecName") = comm.GetSpecNameWithoutProduct(row("specName").ToString().Trim())
            If row.IsNull("specrevision") = True Then
                row("specrevision") = row("specrevision2")
            End If
        Next
        GetWorkflowStep.DBTable = tmpSet.Tables(0).Copy()
    End Function


End Class
